package dao.book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import entity.Borrow;
import utils.ComonUtil;
import utils.SqlConnection;

/**
 * @Author: Lin Hongwei
 * @LastEditors: Lin Hongwei
 * @email: 1365755396@qq.com
 * @CSDN: https://blog.csdn.net/weixin_45429409
 * @Description: 
 *
 */
public class BorrowDaoImpl implements BorrowDao {

	@Override
	public List<Borrow> getBorrwList(Connection con, Borrow borrow) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet res = null;
		List<Borrow> bookList = new ArrayList<Borrow>();
		if (con != null) {
			StringBuffer stringBuffer = new StringBuffer();
			stringBuffer.append("select * from borrow ");
			ArrayList<Object> paramList = new ArrayList<Object>();
	
			if (!ComonUtil.isEmpty(borrow.getBorrowTime())) {
				paramList.add(borrow.getBorrowTime());
				stringBuffer.append("and borrowTime = ? ");
			}
			if (!ComonUtil.isEmpty(borrow.getReturnTime())) {
				paramList.add(borrow.getReturnTime());
				stringBuffer.append("and returnTime = ? ");
			}
		
			
			if(borrow.getFlag()!=-1) {
				paramList.add(borrow.getFlag());
				stringBuffer.append("and flag = ? ");
			}
			if(borrow.getBookId()!=-1) {
				paramList.add(borrow.getBookId());
				stringBuffer.append("and book_id = ? ");
			}			
			
			if (!ComonUtil.isEmpty(borrow.getUserId())) {
				paramList.add(borrow.getUserId());
				stringBuffer.append("and user_id = ? ");
			}
			
			
			if(borrow.getId()!=-1) {
				paramList.add(borrow.getId());
				stringBuffer.append("and id = ? ");
			}
			
			String sql = stringBuffer.toString().replaceFirst("and", "where");
			Object[] params = paramList.toArray();
			System.out.println("查借阅信息："+sql);
			res = SqlConnection.executeQuery(con, pstm, res, sql, params);
			
			while(res.next()) {
				Borrow borrow1 = new Borrow();
				borrow1.setId(res.getInt("id"));
				borrow1.setBookId(res.getInt("book_id"));
				borrow1.setUserId(res.getString("user_id"));
				borrow1.setBorrowTime(res.getString("borrowTime"));
				borrow1.setReturnTime(res.getString("returnTime"));
				borrow1.setFlag(res.getInt("flag"));
				bookList.add(borrow1);
			}
			SqlConnection.closeConnection(con, pstm, res);
		}
		return bookList;
	}

	@Override
	public int addBorrow(Connection con, Borrow borrow) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		int row = 0;

		if(con!=null) {
			String sql = "insert into borrow (user_id,book_id,borrowTime,returnTime,flag) values(?,?,?,?,?)";
			Object[] params = {
					borrow.getUserId(),
					borrow.getBookId(),
					borrow.getBorrowTime(),
					borrow.getReturnTime(),
					borrow.getFlag()
			};
			
			con = SqlConnection.getConnection();
			row = SqlConnection.executeUpdate(con, pstm, sql, params);
			SqlConnection.closeConnection(con, pstm, null);
		}
		
		return row;
	}

	@Override
	public int delBorrow(Connection con, int id) throws Exception {
		// TODO Auto-generated method stub

		PreparedStatement pstm = null;
		int row = 0 ;
		if(con!=null) {
			String sql = "delete from borrow where id = ?";
			Object[] params = {id};
			row = SqlConnection.executeUpdate(con, pstm, sql, params);
		}
		return row;
	}

	@Override
	public int upBorrow(Connection con, Borrow borrow) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm =null;
		int row = 0;
		
		if(con!=null) {
			StringBuffer stringBuffer = new StringBuffer();
			stringBuffer.append("update borrow set ");
			ArrayList<Object> paramsList = new ArrayList<Object>();
			
			
			if (!ComonUtil.isEmpty(borrow.getBorrowTime())) {
				paramsList.add(borrow.getBorrowTime());
				stringBuffer.append("borrowTime = ?,");
			}
			if (!ComonUtil.isEmpty(borrow.getReturnTime())) {
				paramsList.add(borrow.getReturnTime());
				stringBuffer.append("returnTime = ?,");
			}
		
			
			if(borrow.getFlag()!=-1) {
				paramsList.add(borrow.getFlag());
				stringBuffer.append("flag = ?,");
			}
			if(borrow.getBookId()!=-1) {
				paramsList.add(borrow.getBookId());
				stringBuffer.append("book_id = ?,");
			}
			
			if (!ComonUtil.isEmpty(borrow.getUserId())) {
				paramsList.add(borrow.getUserId());
				stringBuffer.append("user_id = ?,");
			}
			

			
			String sql = stringBuffer.toString();
//			System.out.println(sql.substring(sql.length()-1));
			if(sql.substring(sql.length()-1).equals(",")) {
				sql = sql.substring(0,sql.length()-1);
			}
			sql+=" where id = ?";
			paramsList.add(borrow.getId());
			System.out.println("修改语句："+sql);
			
			Object[] params = paramsList.toArray();
			row = SqlConnection.executeUpdate(con, pstm, sql, params);
			SqlConnection.closeConnection(con, pstm, null);
		}
		
		
		return row;
	}

	@Override
	public int getBorrowCount(Connection con, Borrow borrow) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet res = null;
		int count = 0;
//		List<Borrow> bookList = new ArrayList<Borrow>();
		if (con != null) {
			StringBuffer stringBuffer = new StringBuffer();
			stringBuffer.append("select count(*) as count from borrow ");
			ArrayList<Object> paramList = new ArrayList<Object>();

			if(borrow.getFlag()!=-1) {
				paramList.add(borrow.getFlag());
				stringBuffer.append("and flag = ? ");
			}

			if (!ComonUtil.isEmpty(borrow.getUserId())) {
				paramList.add(borrow.getUserId());
				stringBuffer.append("and user_id = ? ");
			}
			
			String sql = stringBuffer.toString().replaceFirst("and", "where");
			System.out.println(sql);
			Object[] params = paramList.toArray();
			res = SqlConnection.executeQuery(con, pstm, res, sql, params);
			
			while(res.next()) {
				count = res.getInt("count");
			}
			SqlConnection.closeConnection(con, pstm, res);
		}
		return count;
	}
}
